﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TarreOA.TarreOAModels;

namespace TarreOA.TarreOADAL
{
    public class UserCommunicationGrouptbDAO
    {
        /// <summary>
        /// 保存新建的通讯组信息
        /// </summary>
        /// <param name="comGroupName"></param>
        /// <param name="comGroupType"></param>
        /// <param name="UserLoginName"></param>
        /// <returns></returns>
        public int saveComGroupInfo(string comGroupName, string comGroupType, string userLoginName, DateTime createTime)
        {
            int cgid = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "insert into UserCommunicationGrouptb values(@cgName, @cgType, @UserLoginName, @createTime)";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@cgName", comGroupName));
                sqlCmd.Parameters.Add(new SqlParameter("@cgType", comGroupType));
                sqlCmd.Parameters.Add(new SqlParameter("@UserLoginName", userLoginName));
                sqlCmd.Parameters.Add(new SqlParameter("@createTime", createTime));
                sqlCmd.ExecuteNonQuery();
                sql = "select @@identity";
                sqlCmd = new SqlCommand(sql, sqlConn);
                cgid = int.Parse(sqlCmd.ExecuteScalar().ToString());
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return cgid;
        }

        /// <summary>
        /// 根据通讯组编号删除通讯组信息
        /// </summary>
        /// <param name="cgid"></param>
        /// <returns></returns>
        public int delUserComGroupInfoById(int cgid)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "delete from UserCommunicationGrouptb where ComGroupId = @cgid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@cgid", cgid));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 根据通讯组编号查找通讯组信息
        /// </summary>
        /// <param name="cgid"></param>
        /// <returns></returns>
        public UserCommunicationGrouptb findByComGroupId(int cgid)
        {
            UserCommunicationGrouptb ucg = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from UserCommunicationGrouptb where ComGroupId = @cgid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@cgid", cgid));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    UserDAO userDAO = new UserDAO();
                    ucg = new UserCommunicationGrouptb();
                    sqlDr.Read();
                    ucg.ComGroupId = (int)sqlDr["ComGroupId"];
                    ucg.ComGroupName = sqlDr["ComGroupName"].ToString();
                    ucg.ComGroupType = sqlDr["ComGroupType"].ToString();
                    ucg.CurUser = userDAO.FindByLoginName(sqlDr["UserLoginName"].ToString());
                    ucg.CreateTime = (DateTime)sqlDr["CreateTime"];
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return ucg;
        }

        /// <summary>
        /// 根据通讯组编号更改通讯组名称
        /// </summary>
        /// <param name="cgid"></param>
        /// <param name="newName"></param>
        /// <returns></returns>
        public int updateComGroupInfoByGroupId(int cgid, string newName)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "update UserCommunicationGrouptb set ComGroupName = @newName where ComGroupId = @cgid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@newName", newName));
                sqlCmd.Parameters.Add(new SqlParameter("@cgid", cgid));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 根据用户登录名查找用户的通讯组信息
        /// </summary>
        /// <param name="UserLoginName"></param>
        /// <returns></returns>
        public List<UserCommunicationGrouptb> findByUserLoginName(string userLoginName)
        {
            List<UserCommunicationGrouptb> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from UserCommunicationGrouptb where UserLoginName = @uln";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@uln", userLoginName));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    UserDAO userDAO = new UserDAO();
                    list = new List<UserCommunicationGrouptb>();
                    while (sqlDr.Read())
                    {
                        UserCommunicationGrouptb userCommunicationGrouptb = new UserCommunicationGrouptb();
                        userCommunicationGrouptb.ComGroupId = (int)sqlDr["ComGroupId"];
                        userCommunicationGrouptb.ComGroupName = sqlDr["ComGroupName"].ToString();
                        userCommunicationGrouptb.ComGroupType = sqlDr["ComGroupType"].ToString();
                        userCommunicationGrouptb.CurUser = userDAO.FindByLoginName(sqlDr["UserLoginName"].ToString());
                        userCommunicationGrouptb.CreateTime = (DateTime)sqlDr["CreateTime"];
                        list.Add(userCommunicationGrouptb);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }
    }
}
